/*
 * 版权所有 (C) 2015 知启蒙(ZHIQIM) 保留所有权利。[遇见知启蒙，邂逅框架梦]
 * 
 * https://zhiqim.org/project/zhiqim_framework/zhiqim_orm.htm
 *
 * Zhiqim Orm is licensed under Mulan PSL v2.
 * You can use this software according to the terms and conditions of the Mulan PSL v2.
 * You may obtain a copy of Mulan PSL v2 at:
 *          http://license.coscl.org.cn/MulanPSL2
 * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
 * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
 * MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
 * See the Mulan PSL v2 for more details.
 */
package org.zhiqim.orm.policy;

import java.io.OutputStream;
import java.io.Writer;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.zhiqim.kernel.model.maps.MapSS;
import org.zhiqim.kernel.util.Arrays;
import org.zhiqim.kernel.util.Asserts;
import org.zhiqim.kernel.util.Classes;
import org.zhiqim.kernel.util.Ints;
import org.zhiqim.kernel.util.Lists;
import org.zhiqim.kernel.util.Sqls;
import org.zhiqim.kernel.util.Strings;
import org.zhiqim.kernel.util.Validates;
import org.zhiqim.orm.ORMException;
import org.zhiqim.orm.ORMI18n;
import org.zhiqim.orm.ORMParameter;
import org.zhiqim.orm.ORMPolicy;
import org.zhiqim.orm.ORMServer;
import org.zhiqim.orm.ORMType;
import org.zhiqim.orm.dbo.defined._Table;
import org.zhiqim.orm.dbo.defined._TableField;
import org.zhiqim.orm.dbo.defined._TableIndex;

/**
 * ORACLE数据库策略
 * 
 * 注意：
 * 1.不支持create table if not exists TABLE风格
 * 2.一条语句最后不要加分号";"，会认为是无效字符ORA-00911，在PL/SQL里需要加分号表示一句结束
 * 3.date类型包括月，年，日，世纪，时，分和秒，不是java.sql.Date只有日期。
 * 4.timestamp类型颗粒到秒后6位，如2012-12-12 12:12:12.000000
 * 
 * 常用类型
 * 1.number(1)      ---  boolean    1/0                         1位
 * 2.number(4)      ---  byte       -255                        4位
 * 3.number(6)      ---  short      -32767                      6位
 * 4.number(11)     ---  int        -2147483648                 11位
 * 5.number(20)     ---  long       -9223372036854775808        20位
 * 6.varchar2(4000)
 *
 * @version v1.0.0 @author zouzhigang 2016-9-18 新建与整理
 */
public class _Oracle implements ORMPolicy
{
    private static final int DURATION_SESSION       = 10;
    private static final int MODE_READWRITE         = 1;
    
    private ORMServer server;
    
    public _Oracle(ORMServer server)
    {
        this.server = server;
    }
    
    /**
     * Oracle对4000字符以上的采用CLOB方案
     * 
     * @param conn              数据库连接
     * @param v                 字符串
     * @return                  CLOB对象
     * @throws SQLException     异常
     */
    public static Clob buildClob(Connection conn, String v) throws SQLException
    {
        try
        {
            /*
            CLOB clob = CLOB.createTemporary(pstmt.getConnection(), false, CLOB.DURATION_SESSION);
            clob.open(CLOB.MODE_READWRITE);
            Writer writer = clob.getCharacterOutputStream();
            writer.write(v);
            writer.close();
            clob.close();
            */
            Class<?> cls = Classes.forName("oracle.sql.CLOB");
            Asserts.as(cls != null?null:"oracle.sql.CLOB类不存在");
            
            Object clob = Classes.invoke(cls, "createTemporary", null, new Object[]{conn, false, DURATION_SESSION});
            Classes.invoke(cls, "open", clob, new Object[] {MODE_READWRITE});
            Writer writer = (Writer)Classes.invoke(cls, "getCharacterOutputStream", clob, new Object[0]);
            writer.write(v);
            writer.close();
            Classes.invoke(cls, "close", clob, new Object[0]);
            
            //返回Clob对象
            return (Clob)clob;
        }
        catch(Throwable e)
        {
            throw new SQLException(e);
        }
    }
    
    /**
     * Oracle对字节存储采用BLOB方案
     * 
     * @param conn              数据库连接
     * @param v                 字节数组
     * @return                  Blob对象
     * @throws SQLException     异常
     */
    public static Blob buildBlob(Connection conn, byte[] v) throws SQLException
    {
        try
        {
            /*
            BLOB blob = BLOB.createTemporary(pstmt.getConnection(), false, BLOB.DURATION_SESSION);
            blob.open(BLOB.MODE_READWRITE);
            OutputStream os = blob.getBinaryOutputStream();
            os.write(v);
            os.close();
            blob.close();
            */
            Class<?> cls = Classes.forName("oracle.sql.BLOB");
            Asserts.as(cls != null?null:"oracle.sql.BLOB类不存在");
                
            Object blob = Classes.invoke(cls, "createTemporary", null, new Object[]{conn, false, DURATION_SESSION});
            Classes.invoke(cls, "open", blob, new Object[] {MODE_READWRITE});
            OutputStream os = (OutputStream)Classes.invoke(cls, "getBinaryOutputStream", blob, new Object[0]);
            os.write(v);
            os.close();
            Classes.invoke(cls, "close", blob, new Object[0]);
            
            //返回Blob对象
            return (Blob)blob;
        }
        catch(Throwable e)
        {
            throw new SQLException(e);
        }
    }
    

    
    
    @Override
    public String chkCreateBefore(ORMParameter parameter)
    {
        return null;
    }
    
    @Override
    public String chkCreateAfter(ORMParameter parameter)
    {
        return null;
    }
    
    @Override
    public boolean isTableNotExist(SQLException e)
    {
        return Validates.isRegexp(T_NOT_EXIST_MYSQL, e.getMessage());
    }
    
    @Override
    public String toExistsSQL(String databaseName, String tableName)
    {
        return Strings.format(T_EXISTS_ORACLE, tableName);
    }
    
    /**
     * 增加表的一列，支持类型和是否为null
     * 
     * @param table         表名
     * @param column        列名
     * @param columnType    列类型
     * @param notNull       是否不允许为null
     * @return              SQL语句
     */
    public String toAlertColumnAdd(String table, String column, String columnType, boolean notNull)
    {
        return new StringBuilder()
            .append("alter table ").append(table.toUpperCase())
            .append(" add column ").append(column.toUpperCase()).append(" ").append(columnType)
            .append(notNull?" not null":" default null")
            .toString();
    }
    
    @Override
    public String toColumnType(String type)
    {
        int ctype = ORMType.getColumnTypeMaybeLength(type);
        switch (ctype)
        {
        //基本类型
        case Z_ORM_BOOLEAN_INT:     return "NUMBER(1)";
        case Z_ORM_BYTE_INT:        return "NUMBER(4)";
        case Z_ORM_SHORT_INT:       return "NUMBER(6)";
        case Z_ORM_INT_INT:         return "NUMBER(11)";
        case Z_ORM_LONG_INT:        return "NUMBER(20)";
        //小数和时间
        case Z_ORM_DATETIME_INT:    return "DATE";
        case Z_ORM_DECIMAL_INT:
        {
            String[] values = Arrays.toStringArray(type);
            Asserts.asserts(values.length == 3, ORMI18n.ormFieldTypeIncorrect1, type);
            Asserts.asserts(Validates.isIntegerPositive(values[1]), ORMI18n.ormFieldTypeIncorrect1, type);
            Asserts.asserts(Validates.isIntegerPositive(values[2]), ORMI18n.ormFieldTypeIncorrect1, type);
            //decimal(15,3)前面11位整数相当于int，中间点号，后面3位小数的表示方式
            return "NUMBER("+values[1]+","+values[2]+")";
            
        }
        //字符串
        case Z_ORM_STRING_INT:
        {
            String[] values = Arrays.toStringArray(type);
            Asserts.asserts(values.length > 1, ORMI18n.ormFieldTypeIncorrect1, type);
            Asserts.asserts(Validates.isIntegerPositive(values[1]), ORMI18n.ormFieldTypeIncorrect1, type);
            
            if (values.length == 3 && "char".equals(values[2]))
            {//char(50)
                Asserts.asserts(Validates.isIntegerValue(values[1], 1, 255), ORMI18n.ormFieldTypeIncorrect1, type);
                return "CHAR("+values[1]+")";
            }
            else
            {//varchar,text
                int length = Ints.toInt(values[1]);
                if (length <= 4000)
                    return "VARCHAR2("+values[1]+")";
                else
                    return "CLOB";
            }
        }
        //二进制字节数组
        case Z_ORM_BINARY_INT:  return "BLOB";
        default:throw Asserts.exception("传入的类型[%s]不支持", type);
        }
    }
    
    @Override
    public String toColumnString(_TableField field)
    {
        String column = field.getColumn();
        String columnType = toColumnType(field.getTypeAndLength());
        boolean notNull = field.isNotNull();
        
        //PARENT_CODE varchar(32) NOT NULL,(逗号在ZTable中处理)
        return new StringBuilder(column.toUpperCase()).append(" ").append(columnType).append(" ").append(notNull?"NOT NULL":"NULL").toString();
    }
    
    @Override
    public List<String> toTableString(_Table _table, MapSS replaceMap)
    {
        String key = _table.getKey();
        
        String tableReplace = Sqls.formatReplaceMap(_table.getTable(), replaceMap);
        List<String> sqlList = new ArrayList<>();
        
        //1.表头
        StringBuilder strb = new StringBuilder();
        strb.append("create table ").append(tableReplace.toUpperCase()).append(_BR_)
            .append("(");//不支持if not exists
        
        //2.字段 PARENT_CODE varchar2(32) NOT NULL
        for (_TableField field : _table.getFieldList())
        {
            strb.append(_BR_).append(_FOUR_).append(toColumnString(field)).append(",");
        }
        
        //3.主键primary key (ITEM_CODE)
        if (_table.getKeyArr().length == 0)
            strb.setLength(strb.length()-1);
        else
            strb.append(_BR_).append(_FOUR_).append("primary key (").append(key).append(")");
        
        //4.表尾
        strb.append(_BR_).append(")").append(_BR_);
        sqlList.add(Sqls.formatSpace(strb.toString()));
        
        
        //5.索引 create unique index IX_USER_INFO_NAME_NICK on USER_INFO(USER_NAME,USER_NICK);
        for (_TableIndex index : _table.getIndexList())
        {
            String indexNameReplace = Sqls.formatReplaceMap(index.getName(), replaceMap);
            StringBuilder strbIndex = new StringBuilder("create ").append(index.getType()).append(" index ").append(indexNameReplace)
                .append(" on ").append(tableReplace).append("(").append(index.getColumn()).append(")")
                .append(_BR_);
            
            sqlList.add(Sqls.formatSpace(strbIndex.toString()));
        }
        
        return Lists.trim(sqlList);
    }
    
    @Override
    public String toItemSQL(String fieldSQL, String tableName, String whereSQL, String orderbySQL, String groupbySQL)
    {
        return new StringBuilder()
        .append("select * from (select ").append(fieldSQL)
        .append(" from ").append(tableName).append(whereSQL).append(orderbySQL).append(groupbySQL)
        .append(") where rownum = 1")
        .toString();
    }
    
    @Override
    public String toPageSQL(String fieldSQL, String tableName, String whereSQL, String orderbySQL, String groupbySQL, int maxNum, int pageNo)
    {
        return new StringBuilder()
            .append("select * from (select rownum idkey_, p_.* from (select ").append(fieldSQL)
            .append(" from ").append(tableName).append(whereSQL).append(orderbySQL).append(groupbySQL)
            .append(") p_) where idkey_ between #minNum# and #maxNum#")
            .toString();
    }
    
    @Override
    public String toPageViewSQL(StringBuilder innerTableSQL, int maxNum, int pageNo)
    {
        return new StringBuilder()
            .append("select * from (select rownum idkey_, p_.* from ").append(innerTableSQL)
            .append(" p_ ) where idkey_ between #minNum# and #maxNum#")
            .toString();
    }
    
    /**************************************************************************************************/
    //不同的数据库处理
    /**************************************************************************************************/
    
    @Override
    public void executeAlertColumnDrop(String table, String column) throws ORMException, SQLException
    {//标准SQL92 drop column
        server.sql().execute(new StringBuilder("alter table ").append(table.toUpperCase()).append(" drop column ").append(column.toUpperCase()).toString());
    }
    
    @Override
    public void executeAlertColumnName(String table, String column, String newColumn) throws ORMException, SQLException
    {//标准SQL92 rename column
        server.sql().execute(new StringBuilder("alter table ").append(table.toUpperCase()).append(" rename column ").append(column).append(" to ").append(newColumn).toString());
    }
    
    @Override
    public void executeAlertColumnType(String table, String column, String newColumnType, boolean newNotNull)throws ORMException, SQLException
    {
        String sql = new StringBuilder()
            .append("alter table ").append(table.toUpperCase()).append(" modify column ").append(column.toUpperCase()).append(" ").append(newColumnType).append(newNotNull?" not null":" default null")
            .toString();
        
        server.sql().execute(sql);
    }
    
    @Override
    public void executeAlertColumnInfo(String table, String column, String newColumn, String newColumnType, boolean newNotNull) throws ORMException, SQLException
    {//分成两句处理
        String sql = new StringBuilder()
            .append("alter table ").append(table.toUpperCase()).append(" rename column ").append(column.toUpperCase()).append(" to ").append(newColumn.toUpperCase()).append(";")
            .append("alter table ").append(table.toUpperCase()).append(" modify column ").append(column.toUpperCase()).append(" ").append(newColumnType).append(newNotNull?" not null":" default null").append(";")
            .toString();
    
        server.sql().execute(sql);
    }
    
    @Override
    public void executeAlertPrimaryKeyAdd(String table, String columns) throws SQLException
    {
        server.sql().execute("alter table "+table+" add primary key("+columns+")");
    }
    
    @Override
    public void executeAlertPrimaryKeyDrop(String table) throws SQLException
    {
        server.sql().execute("alter table "+table+" drop primary key");
    }
}
